from sqlalchemy import BigInteger
from sqlalchemy import Column, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class ZipCode(Base):
    __tablename__ = 'zipcode'

    # create table zipcode(_id bigint AUTO_INCREMENT primary key ,zip_code varchar(20),name varchar(60), area_code varchar(20));
    _id = Column(BigInteger(), primary_key=True)
    zip_code = Column(String(20))
    name = Column(String(60))
    area_code = Column(String(20))


def insertZipCodes(lists):
    # 初始化数据库连接: 格式:'数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'
    engine = create_engine('mysql+mysqlconnector://root:root@localhost:3306/appserver')
    # 创建DBSession类型:
    DBSession = sessionmaker(bind=engine)

    # 创建session对象:
    session = DBSession()

    for item in lists:
        print('district:%s zipCode:%s areaCode:%s' % (item.district, item.zipCode, item.areaCode))
        # 创建新ZipCode对象:
        new_zipcode = ZipCode(_id = None, zip_code=item.zipCode, name=item.district, area_code=item.areaCode)
        # 添加到session:
        session.add(new_zipcode)

    # 提交即保存到数据库:
    session.commit()
    # 关闭session:
    session.close()
